import time

import xlwt

from quote_folder.Db_connect import DB_connect


class UpdateDafu():
    def __init__(self):
        """"""
        self.bigdata_con = DB_connect().online_big_data()  # 数据组数据库连接
        self.bigdata_cur = self.bigdata_con.cursor()
        self.info_list = []

    def main(self):
        """"""
        now_time = time.strftime("%Y%m%d", time.localtime())
        with open("sqh.txt", "r") as fr:
            for sqh in fr:
                sqh = sqh.strip()
                print(f"当前正在获取申请号为：'{sqh}'的状态")
                self.get_status(sqh)
        self.save_excel(self.info_list, now_time)

    def get_status(self, sqh):
        item = {}
        SQH = "ZL" + sqh
        sql = f"SELECT case_status, patent_user, agency FROM xhhg_pantent_status WHERE application_number = '{SQH}'"
        try:
            self.bigdata_cur.execute(sql)
            sql_data = self.bigdata_cur.fetchone()
            item['sqh'] = sqh
            item['status'] = sql_data[0]
            item['patent'] = sql_data[1]
            item['agency'] = sql_data[2]
        except:
            item['sqh'] = sqh
            item['status'] = ''
            item['patent'] = ''
            item['agency'] = ''
        self.info_list.append(item)

    def save_excel(self, info_list, now):
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet("sheet1")
        sheet.write(0, 0, '申请号')
        sheet.write(0, 1, '状态')
        sheet.write(0, 2, '专利权人')
        sheet.write(0, 3, '代理机构')
        if len(info_list) > 0:
            for i in range(len(info_list)):
                sheet.write(i + 1, 0, info_list[i]['sqh'])
                sheet.write(i + 1, 1, info_list[i]['status'])
                sheet.write(i + 1, 2, info_list[i]['patent'])
                sheet.write(i + 1, 3, info_list[i]['agency'])
            workbook.save(f"./{now}答复补正更新状态.xls")


if __name__ == '__main__':
    up = UpdateDafu
    up().main()
